﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using PgBirdie.Core.Config;
using PgBirdie.Core.Extensions;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.AccessControl;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Binary;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Bool;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.DateTime;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Network;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Numeric;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Text;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Unique;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Model.Database.Structure.Index;
using PgBirdie.Core.Service.Infrastructure;

namespace PgBirdie.Core.Service.DbObject
{
    public abstract class DbObjectBase// : IDbObjectFromSqlService
    {
        private readonly ITextFormatService _text;
        protected ITextFormatService Text { get { return _text; } }

        protected DbObjectBase(ITextFormatService text)
        {
            _text = text;
        }

        public virtual string GetCurrentSearchPath(string text)
        {
            // SET search_path = public, pg_catalog;
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
            return segments[3].Remove(segments[3].Length - 1, 1);
        }

        public virtual PgSchema GetCurrentSchema(string currentSearchPath, PgDatabase db)
        {
            if (!db.Schemas.ContainsKey(currentSearchPath))
                throw new Exception(String.Format("Schemas did not contain schema name {0} although it should.",
                                                  currentSearchPath));

            return db.Schemas[currentSearchPath];
        }

        public virtual PgDatabase GetDatabase(string text)
        {
            // For example:
            // CREATE DATABASE "MyDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English, United States' LC_CTYPE = 'English, United States';
            // CREATE DATABASE "MyDatabase";
            
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

            var db = new PgDatabase();
            db.ModelType = PgDbObjectType.Database;
            db.Name = _text.RemoveDoubleQuotes(segments[2]);

            return db;
        }

        public virtual PgSchema GetSchema(string text)
        {
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

            var schema = new PgSchema();
            schema.ModelType = PgDbObjectType.Schema;
            schema.Name = _text.RemoveCommaOrSemiColonFromTheEnd(_text.RemoveDoubleQuotes(segments[2]));

            return schema;
        }

        public abstract PgTable GetTable(string text);

        public virtual string GetSequenceName(string text)
        {
            // Example:
            // CREATE SEQUENCE "SchemaName"."SEQ_TableName"

            return GetDbObjectName(text.SplitBySpaceAndClean()[2]);
        }

        public virtual long GetSequenceStartWith(string text)
        {
            // Example:
            // START WITH 0

            return _text.RemoveDoubleQuotes(text.SplitBySpaceAndClean()[2]).ToLong();
        }

        public virtual long GetSequenceIncrementBy(string text)
        {
            // Example:
            // INCREMENT BY 1

            return _text.RemoveDoubleQuotes(text.SplitBySpaceAndClean()[2]).ToLong();
        }

        public virtual long GetSequenceMinValue(string text)
        {
            // Example:
            // MINVALUE 0

            return _text.RemoveDoubleQuotes(text.SplitBySpaceAndClean()[1]).ToLong();
        }

        public virtual long GetSequenceMaxValue(string text)
        {
            // Example:
            // NO MAXVALUE

            return text.StartsWith("NO") ? long.MaxValue : _text.RemoveDoubleQuotes(text.SplitBySpaceAndClean()[1]).ToLong();
        }

        public virtual long GetSequenceCache(string text)
        {
            // Example:
            // CACHE 1;

            return _text.RemoveDoubleQuotes(text.SplitBySpaceAndClean()[1].Replace(";", String.Empty)).ToLong();
        }

        public virtual PgColumn GetColumn(string text)
        {
            // Examples:
            // "Id" bigint NOT NULL,"
            // "Id" bigint DEFAULT nextval('"SEQ_TableName"'::regclass) NOT NULL,

            var segments = _text.RemoveCommaOrSemiColonFromTheEnd(text)
                .Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

            var column = new PgColumn();
            column.Name = segments[0].Replace("\"", String.Empty);            
            column.Type = GetColumnType(GetColumnDataType(text), segments);            
            column.IsNullable = IsNullable(segments);
            if (segments.Length >= 4 && segments[3].StartsWith("nextval"))
                column.SequenceName = GetColumnSequenceName(segments[3]);

            return column;
        }

        public virtual string GetColumnSequenceName(string text)
        {
            // Example: nextval('"SEQ_TableName"'::regclass)
            return Regex.Match(_text.RemoveCommaOrSemiColonFromTheEnd(text), @"['""](.*)[""']", RegexOptions.Compiled | RegexOptions.IgnoreCase).Value.Replace("\"", String.Empty).Replace("'", String.Empty);
        }

        public virtual ColumnDataType GetColumnDataType(string text)
        {
            // Example: "Name" character varying(100) NOT NULL,
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

            // Binary

            if (segments[1].StartsWith("bytea"))
                return ColumnDataType.ByteA;

            // Boolean
            
            if (segments[1].StartsWith("boolean"))
                return ColumnDataType.Boolean;
            
            // Numeric

            if (segments[1].StartsWith("smallint"))
                return ColumnDataType.SmallInt;
            if (segments[1].StartsWith("integer"))
                return ColumnDataType.Integer;
            if (segments[1].StartsWith("bigint"))
                return ColumnDataType.BigInt;
            if (segments[1].StartsWith("double"))
                return ColumnDataType.Double;

            // Text

            if (segments[1].StartsWith("character"))
            {
                if (segments.Length > 2 && segments[2].StartsWith("varying"))
                    return ColumnDataType.VarChar;
                return ColumnDataType.Char;
            }
            if (segments[1].StartsWith("text"))
                return ColumnDataType.Text;

            // Date & time

            if (segments[1].Equals("date"))
                return ColumnDataType.Date;
            if (segments[1].StartsWith("timestamp"))
                return ColumnDataType.Timestamp;

            // Internet

            if (segments[1].Equals("inet"))
                return ColumnDataType.Inet;

            // Unique

            if (segments[1].StartsWith("uuid"))
                return ColumnDataType.Uuid;

            throw new Exception("Missing data type for column.");
        }

        public virtual PgIndex GetIndex(string line, string[] segments, bool isClustered)
        {
            var index = new PgIndex();

            var isUnique = segments[1].Equals("UNIQUE");

            index.Name = _text.RemoveDoubleQuotes(isUnique ? segments[3] : segments[2]);
            index.Type = isUnique ? segments[7] : segments[6];
            index.IsClustered = isClustered;
            index.IsUnique = isUnique;            
            index.Columns = GetIndexedColumns(line);

            return index;
        }

        public virtual PgConstraintBase GetConstraint(List<string> segments, PgTable table)
        {
            if (segments[0].Equals("ALTER"))
                segments.RemoveRange(0, 3);

            var name = GetConstraintName(segments);

            if (segments[3].Equals("PRIMARY"))
                return new PgPrimaryKey(name, table, ConstraintType.PrimaryKey,
                                        GetPrimaryKeyReferences(segments));

            if (segments[3].Equals("UNIQUE"))
                return new PgUniqueKey(name, table, ConstraintType.UniqueKey,
                                       GetUniqueKeyReferences(segments));

            if (segments[3].Equals("FOREIGN"))
                return new PgForeignKey(name, table, ConstraintType.ForeignKey,
                                        GetForeignKeySourceColumnName(segments),
                                        GetForeignKeyReferenceTableName(segments),
                                        GetForeignKeyReferenceColumnName(segments),
                                        GetForeignKeyMatchType(segments));

            throw new Exception("Missing constraint type.");
        }

        public abstract PgOwner GetOwner(string text, PgDbObject target);
        //{
        //    throw new NotImplementedException("GetOwner not implemented");
        //}

        public virtual PgDbObjectType GetDbObjectTypeFromPrivilege(string text)
        {
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
            return (PgDbObjectType)Enum.Parse(typeof(PgDbObjectType), segments[3], true);
        }

        public virtual string GetDbObjectNameFromPrivilege(string text)
        {
            var segments = text.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
            return _text.RemoveDoubleQuotes(segments[4]);
        }

        public virtual PgPrivilege GetPrivilege(string text, object targetDbObject)
        {
            // Examples:
            // REVOKE ALL ON TABLE "TableName" FROM PUBLIC;
            // REVOKE ALL ON TABLE "TableName" FROM postgres;
            // GRANT ALL ON TABLE "TableName" TO postgres;
            // GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE "TableName" TO myuser;

            var segments = (_text.RemoveCommaOrSemiColonFromTheEnd(text))
                .Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

            var role = new PgRole() { Name = _text.RemoveDoubleQuotes(segments[segments.Length - 1]) };
            var commandType = (PgPrivilegeCommandType)Enum.Parse(typeof(PgPrivilegeCommandType), segments[0], true);
            var rawTypes = segments[1].Split(Settings.SplitterFor.Comma, StringSplitOptions.RemoveEmptyEntries);
            var types = new List<PgPrivilegeType>();
            foreach (var rawType in rawTypes)
                types.Add((PgPrivilegeType)Enum.Parse(typeof(PgPrivilegeType), rawType, true));

            var privilege = new PgPrivilege(role, targetDbObject, commandType, types);

            return privilege;
        }

        private PgColumnTypeBase GetColumnType(ColumnDataType dataType, string[] segments)
        {
            switch (dataType)
            {
                case ColumnDataType.BigInt:
                    return new PgBigInt();
                case ColumnDataType.Boolean:
                    return new PgBoolean();
                case ColumnDataType.ByteA:
                    return new PgByte();
                case ColumnDataType.Char:
                    return new PgCharacter(GetCharColumnLength(segments));
                case ColumnDataType.Date:
                    return new PgDate();
                case ColumnDataType.Double:
                    return new PgDouble();
                case ColumnDataType.Inet:
                    return new PgInet();
                case ColumnDataType.Integer:
                    return new PgInteger();
                case ColumnDataType.SmallInt:
                    return new PgSmallInt();
                case ColumnDataType.Text:
                    return new PgText();
                case ColumnDataType.Timestamp:
                    return new PgTimestamp(WithTimeZone(segments), GetPrecisionForTimeAndTimestamp(segments));
                case ColumnDataType.Uuid:
                    return new PgUuid();
                case ColumnDataType.VarChar:
                    return new PgVarChar(GetVarCharColumnLength(segments));
            }

            throw new Exception("Could not get column type.");
        }

        private long GetCharColumnLength(string[] segments)
        {
            return ExtractColumnLength(segments[1]);
        }

        private long GetVarCharColumnLength(string[] segments)
        {
            return ExtractColumnLength(segments[2]);
        }

        private bool WithTimeZone(string[] segments)
        {
            return segments[2].Equals("with");
        }

        private int GetPrecisionForTimeAndTimestamp(string[] segments)
        {
            var timestamp = segments[1];
            if (!timestamp.Contains("(")) // ( indicates timestamp contains precision.
                return 0;

            int value;
            Int32.TryParse(timestamp.Split(new[] { "(" }, StringSplitOptions.None)[1].Replace(")", String.Empty),
                           out value);
            return value;
        }

        private bool IsNullable(string[] segments)
        {
            if (segments[segments.Length - 2].Equals("NOT"))
                return false;
            return true;
        }

        private long ExtractColumnLength(string text)
        {
            long value;
            var temp = text.Split(new[] { '(' })[1].Replace(")", String.Empty);
            Int64.TryParse(temp, out value);
            if (value != 0)
                return value;
            throw new Exception("Could not extract column length.");
        }

        private IList<string> GetPrimaryKeyReferences(IList<string> segments)
        {
            var columnNames = new List<string>();

            // Start looking after ADD CONSTRAINT "ConstraintName" PRIMARY KEY.
            for (int i = 5; i < segments.Count; i++)
            {
                var name = segments[i].Replace("(", String.Empty).Replace("\"", String.Empty).Replace(")", String.Empty).Replace(
                    ",", String.Empty);
                columnNames.Add(name);
            }

            return columnNames;
        }

        private IList<string> GetUniqueKeyReferences(IList<string> segments)
        {
            var columnNames = new List<string>();

            // Start looking after ADD CONSTRAINT "ConstraintName" UNIQUE.
            for (int i = 4; i < segments.Count; i++)
            {
                var name = segments[i].Replace("(", String.Empty).Replace("\"", String.Empty).Replace(")", String.Empty).Replace(
                    ",", String.Empty);
                columnNames.Add(name);
            }

            return columnNames;
        }

        // ADD CONSTRAINT "ConstraintName" FOREIGN KEY ("SourceColumn") REFERENCES "ReferenceTable"("ReferenceColumn") ...
        private string GetForeignKeySourceColumnName(IList<string> segments)
        {
            return segments[5].Replace("(", String.Empty).Replace("\"", String.Empty).Replace(")", String.Empty);
        }

        private string GetForeignKeyReferenceTableName(IList<string> segments)
        {
            var reference = segments[7].Split(new[] { '(' });
            return reference[0].Replace("\"", String.Empty);
        }

        private string GetForeignKeyReferenceColumnName(IList<string> segments)
        {
            // Examples:
            // pgdump
            // ADD CONSTRAINT "FK_SourceTable_ForeignTable" FOREIGN KEY ("SourceTableColumn") REFERENCES "ForeignTable"("ForeignTableColumn");
            // Source control
            // ADD CONSTRAINT "FK_SourceTable_ForeignTable" FOREIGN KEY ("SourceTableColumn") REFERENCES "ForeignTable" ("ForeignTableColumn")

            if (segments.Count >= 9 && segments[8].StartsWith("("))
                return segments[8].Replace("(", String.Empty).Replace(")", String.Empty).Replace("\"", String.Empty);
            return segments[7].Split(new[] { '(' })[1].Replace(")", String.Empty).Replace("\"", String.Empty);
        }

        private string GetForeignKeyMatchType(IList<string> segments)
        {
            return "SIMPLE";
        }

        private string GetConstraintName(IList<string> segments)
        {
            return segments[2].Replace("\"", String.Empty);
        }

        private IList<PgIndexColumn> GetIndexedColumns(string line)
        {
            // CREATE INDEX "IX_Entity_Name" ON "Entity" USING btree ("Name");
            // CREATE UNIQUE INDEX "UIX_Entity_Name" ON "Entity" USING btree ("Name", "Description", "Something");
            // CREATE UNIQUE INDEX "UIX_Entity_Name" ON "Entity" USING btree (lower("Name"));
            // CREATE INDEX "IX_Entity_Name" ON "Entity" USING btree ("Name" DESC NULLS LAST);
            // CREATE UNIQUE INDEX "UIX_Entity_Name" ON "Entity" USING btree (lower("Name") DESC NULLS LAST, "Date" DESC NULLS FIRST);

            var columns = new List<PgIndexColumn>();

            var matchColumns = Regex.Match(line, @"\((.*)\);");
            if (!matchColumns.Success)
                throw new Exception("Could not find columns for index.");
            
            var cols = matchColumns.Groups[1].Captures[0].ToString().SplitBy(",");

            foreach (var col in cols)
            {
                // ^(?<function>lower ?)?\(? ?"?(?<name>[0-9a-zA-Z_]+)"? ?\)? ?(?<ordering>DESC NULLS LAST|DESC NULLS FIRST)?$
                var matchColumnDefinition = Regex.Match(col, @"^(?<function>lower ?)?\(? ?""?(?<name>[0-9a-zA-Z_]+)""? ?\)? ?(?<ordering>DESC NULLS LAST|DESC NULLS FIRST)?$");
                if (!matchColumnDefinition.Success)
                    throw new Exception("Could not determine column index definition.");

                var column = new PgIndexColumn();

                if (matchColumnDefinition.Groups["function"].Success)
                    column.Function = matchColumnDefinition.Groups["function"].Captures[0].ToString();

                if (matchColumnDefinition.Groups["name"].Success)
                    column.Name = matchColumnDefinition.Groups["name"].Captures[0].ToString();

                if (matchColumnDefinition.Groups["ordering"].Success)
                {
                    var ordering = matchColumnDefinition.Groups["ordering"].Captures[0].ToString();
                    column.IsDescending = !string.IsNullOrWhiteSpace(ordering) && ordering.StartsWith("DESC", StringComparison.OrdinalIgnoreCase);
                    column.NullsFirst = !string.IsNullOrWhiteSpace(ordering) && ordering.EndsWith("FIRST", StringComparison.OrdinalIgnoreCase);
                }
                
                columns.Add(column);
            }

            return columns;
        }

        private string GetDbObjectName(string schemaNameDotDbObjectName)
        {
            var parts = schemaNameDotDbObjectName.SplitByPeriod();
            if (parts.Count == 1)
                return _text.RemoveDoubleQuotes(parts[0]);
            if (parts.Count == 2)
                return _text.RemoveDoubleQuotes(parts[1]);
            throw new ArgumentException("schemaNameDotDbObjectName");
        }
    }
}
